package com.sfilyh.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class DBUtil {
	public static QueryRunner qr;
	
	/**
	 * 查询返回LIST MAP 集合
	 * @param sql  
	 * @param args
	 * @return
	 */
	public static List<Map<String,Object>>  queryForListMap(String sql,Object... args){
		MapListHandler rsh   = new MapListHandler();		
		try {
			return qr.query(sql, rsh, args);
		} catch (SQLException e) {
			e.printStackTrace();			
		}
		return null;		
	}
	
	/**
	 * 查询返回MAP
	 * @param sql
	 * @param args
	 * @return
	 */
	public static Map<String,Object> queryForMap(String sql,Object... args){
		try {
			return qr.query(sql, new MapHandler(), args);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	

	/***
	 * 统计SQL返回数量
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int countResutSize(String sql ,Object... params){
		StringBuilder sb = new StringBuilder();
		sb.append(" select count(1) from (");
		sb.append(sql);
		sb.append(") A");
		ScalarHandler<Long> rsh = new ScalarHandler<Long>(1);		
		try {			
			Long c = qr.query(sb.toString(), rsh, params);			
			return c.intValue();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}	
	
	/**
	 * 更新
	 * @param sql
	 * @param params
	 * @return
	 */
	public static boolean  update(String sql,Object ... params){
		try {
			if(qr.update(sql, params) > 0){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} ;
		return  false;
	}
	
	/**
	 * 执行insert并返回自增id
	 * @param sql
	 * @param params
	 * @return 成功 生成id 失败 －1
	 */
	public static int updateReturnInsertId(String sql ,Object... params){
		Connection conn = null;
		int id = -1;
		try {
			conn = qr.getDataSource().getConnection();
			PreparedStatement pst = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			
			for(int i = 0 ;i < params.length ;i++){
				pst.setObject(i+1, params[i]);
			}
			pst.executeUpdate();
			
			ResultSet rs = pst.getGeneratedKeys();
			if(rs.next()){
				id = rs.getInt(1);
			}		
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return id;
	}
}
